﻿using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace DataAccess.Tests
{
    [TestClass()]
    public class PostgreSql93DatabaseTransactionTest
    {
        private TestContext testContextInstance;

        public TestContext TestContext
        {
            get
            {
                return testContextInstance;
            }
            set
            {
                testContextInstance = value;
            }
        }

        #region Additional test attributes
        // 
        //You can use the following additional attributes as you write your tests:
        //
        //Use ClassInitialize to run code before running the first test in the class
        //[ClassInitialize()]
        //public static void MyClassInitialize(TestContext testContext)
        //{
        //}
        //
        //Use ClassCleanup to run code after all tests in a class have run
        //[ClassCleanup()]
        //public static void MyClassCleanup()
        //{
        //}
        //
        //Use TestInitialize to run code before running each test
        //[TestInitialize()]
        //public void MyTestInitialize()
        //{
        //}
        //
        //Use TestCleanup to run code after each test has run
        //[TestCleanup()]
        //public void MyTestCleanup()
        //{
        //}
        //
        #endregion

        readonly string connectionName = "PostgreSQLDataAccessTest.LocalTransactionTest.ConnectionString";

        internal class Account
        {
            public int AccountId { get; set; }
            public decimal Amount { get; set; }
        }

        [TestMethod()]
        public void DataAccessLocalTransactionTest()
        {
            // Test script executor (create database)
            ScriptExecutor.ExecuteScript(ConnectionManager.GetConnection("PostgreSQLMaster"),

@"
DROP DATABASE IF EXISTS ""LocalTransactionTest"";

CREATE DATABASE ""LocalTransactionTest""
  WITH OWNER = ""user""
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'English_United States.1252'
       LC_CTYPE = 'English_United States.1252'
       CONNECTION LIMIT = -1;
", 
            ";");

            // Test script executor (create table)
            ScriptExecutor.ExecuteScript(ConnectionManager.GetConnection(connectionName),
@"
DROP TABLE IF EXISTS ""CheckingAccount"";

CREATE TABLE ""CheckingAccount""(
    ""AccountId"" INT NOT NULL,
    ""Amount"" MONEY
);

ALTER TABLE ""CheckingAccount""
ADD CONSTRAINT CheckingAccount_PK PRIMARY KEY (""AccountId"");

DROP TABLE IF EXISTS ""SavingAccount"";

CREATE TABLE ""SavingAccount""(
    ""AccountId"" INT NOT NULL,
    ""Amount"" MONEY
);

ALTER TABLE ""SavingAccount""
ADD CONSTRAINT SavingAccount_PK PRIMARY KEY (""AccountId"");

INSERT INTO ""CheckingAccount"" (""AccountId"", ""Amount"") VALUES (1, 1000);

INSERT INTO ""SavingAccount"" (""AccountId"", ""Amount"") VALUES (1, 500);
", 
            ";");

            // Transaction with successfull commit

            Factory
                .Transaction()
                .IsLocal()
                .ReadCommitted()
                .Connection(connectionName)
                .Command( // Withdraw 300 from the checking account
                    Factory
                        .Command()
                        .Text(@"UPDATE ""CheckingAccount"" SET ""Amount"" = ""Amount"" - 300::money WHERE ""AccountId"" = :accountId")
                        .Parameter("accountId", 1)
                )
                .Command( // Deposit 300 into the saving account
                    Factory
                        .Command()
                        .Text(@"UPDATE ""SavingAccount"" SET ""Amount"" = ""Amount"" + 300::money WHERE ""AccountId"" = :accountId")
                        .Parameter("accountId", 1)
                )
                .Execute();

            var checkingCommand = Factory
                .Command()
                .Connection(connectionName)
                .Text(@"SELECT ""Amount"" FROM ""CheckingAccount"" WHERE ""AccountId"" = :accountId")
                .Parameter("accountId", 1)
                .ToQueryObject<Account>((reader, account) =>
                {                  
                    account.AccountId = 1;
                    account.Amount = reader.GetDecimal(0);
                });

            checkingCommand.Execute();

            Account checking = checkingCommand.Object;

            Assert.AreEqual(700m, checking.Amount);

            var savingCommand = Factory
                .Command()
                .Connection(connectionName)
                .Text(@"SELECT ""Amount"" FROM ""SavingAccount"" WHERE ""AccountId"" = :accountId")
                .Parameter("accountId", 1)
                .ToQueryObject<Account>((reader, account) =>
                {
                    account.AccountId = 1;
                    account.Amount = reader.GetDecimal(0);
                });

            savingCommand.Execute();

            Account saving = savingCommand.Object;

            Assert.AreEqual(800m, saving.Amount);

            // Transaction with rollback

            bool failed = false;

            try
            {
                Factory
                    .Transaction()
                    .IsLocal()
                    .ReadCommitted()
                    .Connection(connectionName)
                    .Command( // Withdraw 300 from the checking account
                        Factory
                            .Command()
                            .Text(@"UPDATE ""CheckingAccount"" SET ""Amount"" = ""Amount"" - 300::money WHERE ""AccountId"" = :accountId")
                            .Parameter("accountId", 1)
                    )
                    .Command( // Deposit 300 into the saving account but comment the parameter so it will fail
                        Factory
                            .Command()
                            .Text(@"UPDATE ""SavingAccount"" SET ""Amount"" = ""Amount"" + 300::money WHERE ""AccountId"" = :accountId")
                    //.Parameter("accountId", 1)
                    )
                    .Execute();
            }
            catch
            {
                failed = true;
            }

            Assert.IsTrue(failed);

            var response = checkingCommand.QueryObject<Account>((reader, account) =>
            {
                account.AccountId = 1;
                account.Amount = reader.GetDecimal(0);
            });

            checking = response.Data;

            Assert.AreEqual(700m, checking.Amount);

            response = savingCommand.QueryObject<Account>((reader, account) =>
            {
                account.AccountId = 1;
                account.Amount = reader.GetDecimal(0);
            });

            saving = response.Data;

            Assert.AreEqual(800m, saving.Amount);
        }
    }
}
